
CREATE TABLE UserInfo (
UserID			INT	IDENTITY(1,1)	CONSTRAINT UserInfo_UserID_PK		PRIMARY KEY,
FirstName		NVARCHAR(20)		NOT NULL,
LastName		NVARCHAR(20)		NOT NULL,
SecuritySalt	BINARY(20)			NOT NULL,
SecurityKey		BINARY(20)			NOT NULL,
Email			NVARCHAR(256)		NOT NULL,
Phone			NVARCHAR(20),
Addline1		NVARCHAR(30),
Addline2		NVARCHAR(30),
City			NVARCHAR(20),
State			NCHAR(2),
ZipCode			NVARCHAR(10),
SecurityRole	NVARCHAR(7)			NOT NULL)
GO

CREATE TABLE StaffInfo (
EducationLevel	NVARCHAR(20),
Salary			DECIMAL(10,2),
Location		NVARCHAR(20),
JobCategory		NVARCHAR(20),
UserID			INT)
GO

ALTER TABLE StaffInfo
	ADD CONSTRAINT StaffInfo_UserID_FK FOREIGN KEY (UserID) REFERENCES UserInfo
GO

CREATE TABLE ContractInfo (
ContractID	INT	IDENTITY(1,1)	CONSTRAINT ContractInfo_ContractID_PK	PRIMARY KEY,
StartDate	DATETIME,
EndDate		DATETIME,
ClientID	INT,
ManagerID	INT)
GO

ALTER TABLE ContractInfo
	ADD CONSTRAINT ContractInfo_ClientID_FK FOREIGN KEY (ClientID) REFERENCES UserInfo
GO
ALTER TABLE CONTRACTINFO
	ADD CONSTRAINT ContractInfo_ManagerID_FK FOREIGN KEY (ManagerID) REFERENCES UserInfo
GO

CREATE TABLE StaffRequest (
RequestID	INT	IDENTITY(1,1)	CONSTRAINT StaffRequest_RequestID_PK	PRIMARY KEY,
ContractID	INT		NOT NULL,
Staff1		INT		NOT NULL,
Staff2		INT,
Staff3		INT)
GO

ALTER TABLE StaffRequest
	ADD CONSTRAINT StaffRequest_ContractID_FK FOREIGN KEY (ContractID) REFERENCES ContractInfo
GO
ALTER TABLE StaffRequest
	ADD CONSTRAINT StaffRequest_Staff1_FK FOREIGN KEY (Staff1) REFERENCES UserInfo
GO
ALTER TABLE StaffRequest
	ADD CONSTRAINT StaffRequest_Staff2_FK FOREIGN KEY (Staff2) REFERENCES UserInfo
GO
ALTER TABLE StaffRequest
	ADD CONSTRAINT StaffRequest_Staff3_FK FOREIGN KEY (Staff3) REFERENCES UserInfo
GO

CREATE TABLE Skill (
SkillID		INT	IDENTITY(1,1)	CONSTRAINT Skill_SkillID_PK		PRIMARY KEY,
Name		NVARCHAR(30))
GO

CREATE TABLE UserSkill (
UserID	INT	NOT NULL,
SkillID	INT NOT NULL)
GO

ALTER TABLE UserSkill
	ADD CONSTRAINT UserSkill_UserID_FK FOREIGN KEY (UserID) REFERENCES UserInfo
GO
ALTER TABLE UserSkill
	ADD CONSTRAINT UserSkill_SkillID_FK FOREIGN KEY (SkillID) REFERENCES Skill
GO